package com.gzdh.tang.modu.controller;

import com.alipay.api.internal.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ExcelTest {


    public static void main(String[] args) {


        try {
            //创建工作簿
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("D:\\导入模板.xlsx"));
            System.out.println("xssfWorkbook对象：" + xssfWorkbook);
            //读取第一个工作表(这里的下标与list一样的，从0开始取，之后的也是如此)
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
            System.out.println("sheet对象：" + sheet);
            System.out.println("sheet名称：" + sheet.getSheetName());
            System.out.println("sheet总数:" + xssfWorkbook.getNumberOfSheets());

            //获取最后一行的num，即总行数。此处从0开始计数
            int maxRow = sheet.getLastRowNum();
            System.out.println("总行数为：" + maxRow);
            //获取第一行的数据
            int maxRew = sheet.getRow(0).getLastCellNum();
            System.out.println("获取行数：" + maxRew);


            String[][] tables = new String[maxRow + (maxRew / 3) + 1][maxRew];

            String[] nr = new String[1000];

            for (int row = 0; row <= maxRow; row++) {
                //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
                int maxRol = sheet.getRow(row).getLastCellNum();


//                System.out.println("--------第" + row + "行的数据如下--------");
                for (int rol = 0; rol < maxRol; rol++) {
//                    System.out.print(sheet.getRow(row).getCell(rol) + " " + ((rol + 1) % 4 == 0 ? "|" : ""));
                    try {
//                        System.out.println(sheet.getRow(row).getCell(rol).getCellType());
//                        System.out.println(HSSFDateUtil.isCellDateFormatted(sheet.getRow(row).getCell(rol)));  ;
//                        System.out.println(getCellValue(sheet.getRow(row).getCell(rol)));
//                        tables[row][rol] = sheet.getRow(row).getCell(rol).toString();
                        tables[row][rol] = getCellValue(sheet.getRow(row).getCell(rol));
                    } catch (Exception e) {
                        tables[row][rol] = "";
                    }

                }
//                System.out.println();
            }

            int rowTemp = 0;
            for (int redRol = 0; redRol < maxRew; redRol += 3) {
                nr[rowTemp++] = "========START========";
                for (int i = 0; i < maxRow; i++) {
//                    System.out.println();
 //                    nr[rowTemp++] = tables[i][redRol] + "\t" + tables[i][redRol + 1] + "\t" + tables[i][redRol + 2] + "\t" + tables[i][redRol + 3];
                    String rol1 = tables[i][redRol];
                    String rol2 = tables[i][redRol + 1];
                    String rol3 = tables[i][redRol + 2];
//                    String rol4 = tables[i][redRol + 3];
                    if (StringUtils.isEmpty(rol1) && StringUtils.isEmpty(rol2) && StringUtils.isEmpty(rol3)) {
                        continue;
                    }
                    if ("序号".equals(rol1) && "名称".equals(rol2) && "单价".equals(rol3)) {
                        continue;
                    }
                    nr[rowTemp++] = rol1 + "\t" + rol2 + "\t" + rol3 + "\t";

                    try {
                        String rol11 = tables[i + 2][redRol];
                        String rol12 = tables[i + 2][redRol + 1];
                        String rol13 = tables[i + 2][redRol + 2];
//                        String rol14 = tables[i + 2][redRol + 3];
                        if ("序号".equals(rol11) && "名称".equals(rol12) &&  "单价".equals(rol13)) {
                            nr[rowTemp++] = "========DINNER*START========";
                        }
                    } catch (Exception e) {

                    }
                }
//                nr[rowTemp++] = "========END========";
            }
            for (int i = 0; i < rowTemp; i++) {
                System.out.println(nr[i]);
            }

//            XSSFRow row = sheet.getRow(0);
//            System.out.println("row对象：" + row);
//            //获取该行第一个单元格的数据
//            XSSFCell cell0 = row.getCell(0);
//            System.out.println("cello对象：" + cell0);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 数据导入处理 获取单元格值
     *
     * @param row    获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    public static String getCellValue(Cell cell) {
        Object val = "";

        try {

            if (cell != null) {
                /**
                 * format 的值可能为以下这些 yyyyMMddHHmmss
                 * yyyy-MM-dd----- 14
                 * yyyy年m月d日----- 31
                 * yyyy年m月--------57
                 * m月d日  -----------58
                 * HH:mm-----------20
                 * h时mm分  --------- 32
                 */
                if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA) {
                    val = cell.getNumericCellValue();
                    // POI Excel 日期格式转换
                    String formatDate = "";
                    switch (cell.getCellStyle().getDataFormat()) {
                        case 14:
                            formatDate = "yyyy-MM-dd";
                            break;
                        case 20:
                            formatDate = "HH:mm";
                            break;
                        case 21:
                            formatDate = "HH:mm:ss";
                            break;
                        case 31:
                            formatDate = "yyyy年MM月dd日";
                            break;
                        case 32:
                            formatDate = "HH时mm分";
                            break;
                        case 33:
                            formatDate = "HH时mm分mm秒";
                            break;
                        case 57:
                            formatDate = "yyyy年MM月";
                            break;
                        case 58:
                            formatDate = "MM月dd日";
                            break;
//                        case 176:
//                            formatDate = "yyyy-MM-dd";
//                            break;
                    }
                    if (!"".equals(formatDate)) {
                        val = new SimpleDateFormat(formatDate).format(DateUtil.getJavaDate((Double) val));
//                        System.out.println(val);
                    } else {
                        if ((Double) val % 1 != 0) {
                            val = new BigDecimal(val.toString()).stripTrailingZeros();
                        } else {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                } else if (cell.getCellType() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellType() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellType() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return String.valueOf(val);
        }
        return String.valueOf(val);
    }
}
